package com.kfd.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.kfd.exception.DuplicateCodeException;
import com.kfd.exception.DuplicateNumberException;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Properties;

/**
 * 如果需要使用反射处理结果集，数据库中列的名字必须和实体类属性的名字一致
 * jdbc工具类
 */
public class JDBCutil {
    //工具类对象
    protected Connection connection = null;
    protected PreparedStatement preparedStatement = null;
    protected ResultSet resultSet = null;
    //受影响的行数
    protected int count = 0;
    //连接信息
    protected static String url = "";
    protected static String username = "";
    protected static String password = "";
    protected static String dirver = "";
    protected static String maxActive = "";
    protected static String maxWait = "";

    //德鲁伊
    protected static DruidDataSource druidDataSource = new DruidDataSource();

    static {
        //读取配置文件,更改为自动读取c3p0xml配置文件
        try {
            InputStream inputStream = JDBCutil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            dirver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("user");
            password = properties.getProperty("password");
            maxActive = properties.getProperty("maxActive");
            maxWait = properties.getProperty("maxWait");
            druidDataSource.setUrl(url);
            druidDataSource.setUsername(username);
            druidDataSource.setPassword(password);
            druidDataSource.setDriverClassName(dirver);
            druidDataSource.setMaxActive(Integer.valueOf(maxActive));
            druidDataSource.setMaxWait(Long.valueOf(maxWait));

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     *
     * @return Connection
     */
    protected Connection getConnection() {
        try {
            //德鲁伊
            connection = druidDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 创建通道
     *
     * @param sql sql语句
     * @return 通道
     */
    protected PreparedStatement getPreparedStatement(String sql) {
        connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }

    /**
     * 为占位符赋值
     *
     * @param list list集合中数据添加顺序需要和占位符从前到后顺序一致
     */
    protected void setPlaceholder(List list) {
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                try {
                    preparedStatement.setObject(i + 1, list.get(i));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 增删改sql工具
     *
     * @param sql  sql语句
     * @param list 为占位符赋值的集合，数据添加顺序和占位符顺序一致
     * @return 返回受影响的行数
     */
    protected int update(String sql, List list) throws DuplicateNumberException, DuplicateCodeException {
        getPreparedStatement(sql);
        setPlaceholder(list);
        try {
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            if(e.getMessage().endsWith("for key 'u_number'")){
                DuplicateNumberException duplicateNumberException = new DuplicateNumberException(e.getMessage());
                throw duplicateNumberException;
            }else if(e.getMessage().endsWith("for key 'u_code'")){
                DuplicateCodeException duplicateCodeException = new DuplicateCodeException(e.getMessage());
                throw duplicateCodeException;
            }else{
                e.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 查询sql工具
     *
     * @param sql  sql语句
     * @param list 为占位符赋值的集合，数据添加顺序和占位符顺序一致
     * @return ResultSet结果
     */
    public ResultSet select(String sql, List list) {
        getPreparedStatement(sql);
        setPlaceholder(list);

        try {
            resultSet = preparedStatement.executeQuery();
            return resultSet;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 关闭资源
     */
    protected void close() {
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过反射处理查询后的结果集；可以1表-1实体类映射，也可以1表-多表-（1实体类下对应其他多个实体类）映射，需要字段名和数据库一致
     *
     * @param resultSet 执行查询后的结果集
     * @param cla       主类；（不能为空）
     * @param cla2      子类；（可以为空）
     * @return 返回映射完的实体类结果集
     */
    protected List selectQueryAll(ResultSet resultSet, Class cla, Class cla2) {
        List<Object> list = null;
        try {
            //返回集合
            list = new ArrayList<Object>();
            //查询返回的结果集有多少列
            ResultSetMetaData metaData = resultSet.getMetaData();
            int strip = metaData.getColumnCount();//得到查询结果共多少列
            String[] columnnames = new String[strip];
            for (int i = 0; i < strip; i++) {
                columnnames[i] = metaData.getColumnName(i + 1);//列的名字下标从1开始
            }
            //通过反射获得实体类的set方法
            Method[] declaredMethods = cla.getDeclaredMethods();
            List<Method> declaredMethods2 = new ArrayList<Method>();
            //判断是否有多表
            if (cla2 != null) {
                //将反射获取到的方法，赋给集合
                Collections.addAll(declaredMethods2, cla2.getDeclaredMethods());
            }
            //循环
            while (resultSet.next()) {
                Object instance = cla.newInstance();//调用无参构造方法创建主类对象
                for (String columnname : columnnames) {//循环给第一列的名字加上set
                    String name = "set" + columnname;
                    Object instance2 = null;
                    if (declaredMethods2 != null && declaredMethods2.size() > 0) {
                        instance2 = cla2.newInstance();//调用无参构造方法创建子类对象
                    }
                    //判断是否有多表,有的话获取列值，
                    if (instance2 != null) {
                        for (Method method : declaredMethods2) {//循环获得的实体类的方法
                            if (method.getName().equalsIgnoreCase(name)) {//如果名字相同则执行该方法
                                method.invoke(instance2, resultSet.getObject(columnname));//从resultSet中取值
                                break;
                            }
                        }
                    }
                    for (Method method : declaredMethods) {//循环获得的实体类的方法
                        if (method.getName().equalsIgnoreCase(name)) {//如果名字相同则执行该方法
                            if("QUANTITY".equals(columnname)){
                                method.invoke(instance, resultSet.getInt(2));
                            }else{
                                method.invoke(instance, resultSet.getObject(columnname));//从resultSet中取值
                            }
                        }
                        if (cla2 != null) {
                            if (("set" + cla2.getSimpleName()).equalsIgnoreCase(method.getName())) {
                                method.invoke(instance, instance2);//从resultSet中取值
                            }
                        }
                    }
                }
                list.add(instance);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 实体类中不为空字段自动添加进list（对象中有多个字段时，将对象中的值转移到list中，返回一个特定顺序的list，方便对占位符进行赋值，需要提供占位符顺序）；
     *
     * @param obj    存有值的实体类
     * @param list 占位符顺序
     * @return 返回符合为占位符赋值的list集合
     */
    protected List getList(Object obj, List<String> list) {
        //反射获得实体类所有的属性
        Field[] fields = obj.getClass().getFields();

        //通过反射获得实体类的get方法
        Method[] declaredMethods = obj.getClass().getDeclaredMethods();

        List gather = new ArrayList();

        for (int j = 0; j < list.size(); j++) {
            for (int i = 0; i < declaredMethods.length; i++) {
                if (("get" + list.get(j)).equalsIgnoreCase(declaredMethods[i].getName())) {
                    String invoke = "";
                    try {
                        invoke = String.valueOf(declaredMethods[i].invoke(obj));
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                    if (invoke != null && !invoke.equals("")) {
                        gather.add(invoke);
                    }
                }
            }

        }
        return gather;
    }

}